import hmac,random,string

import flask
import numpy as np
import pandas as pd
from flask import Flask, jsonify, render_template, abort

import dataSolution
import EnvSetting
import sqlLink
import url_solution
import userSign

app = Flask(__name__)
# app = Flask(__name__,instance_relative_config=True)
# app.config.from_pyfile('setting.cfg')

app.config['UPLOAD_FOLDER'] = 'upload/'

@app.route('/test/<name>')
def test(name):
  sql = f"SELECT page_name,intro FROM `{EnvSetting.database}`.`{EnvSetting.content_tab}` where page_name='{name}'"
  content_res = sqlLink.sqlGetData(EnvSetting.database,sql)
  if type(content_res) == str:
    return content_res
  if len(content_res) == 0:
    return None
  else:
    content = content_res[0][1]
  return [name,content]


@app.route('/')
def hello_world():
    return 'Hello World!'

# @app.before_request
# def safe_request():
#   '''
#   放行公共资源请求
#   '''
#   if flask.request.path in ['/dataAll']:
#     return None
@app.before_request
def login_check():
  '''
  检测请求来源
  '''
  # 放行公共资源请求
  suff = flask.request.path.startswith('/test')
  if flask.request.path in ['/dataAll','/'] or suff:
    return None
  # print(flask.request.referrer.split('/')[-2],'请求')
  print(flask.request.form,'form信息',flask.request.data,'data信息')
  if flask.request.path in ['/login','/tokenSign','/register','/checkPic']:
    if flask.request.data and 'clientid' in eval(flask.request.data.decode('utf-8')):
      # res = 'clientid' in eval(flask.request.data.decode('utf-8'))
      return None
    else:
      return abort(418)
  # 用户身份验证
  if flask.request.form:
    submit_dict = flask.request.form.to_dict()
    if 'token' in submit_dict and 'account' in submit_dict:
      verification = userSign.userTokenCheck(submit_dict['account'],submit_dict['token'])
      if type(verification) == str:
        return flask.redirect('/sign'+'?tip={}'.format(verification))
      # 高危访问检测
      if flask.request.path in ['/upload','/Uploader','/Del_file']:
        print(flask.request.query_string,'query',flask.request.args)
        
        return None
    else:
      return flask.redirect('/sign'+'?tip=用户未登录')
  elif flask.request.data:
    submit_dict = eval(flask.request.data.decode('utf-8'))
    if 'token' in submit_dict and 'account' in submit_dict:
      verification = userSign.userTokenCheck(submit_dict['account'],submit_dict['token'])
      if type(verification) == str:
        return verification
    else:
      return '用户未登录'
  else:
    return abort(500)
  

@app.route('/upload',methods=['GET','POST'])
def upload_file():
    #文件上传页面
    import os
    path = "upload/"
    dir_list = os.listdir( path )
    # if flask.request.args:
    #   #获取参数
    #   args = flask.request.args['tip']
    #   return render_template('upload.html',files=dir_list,tip=args)
    # else:
    #   return render_template('upload.html',files=dir_list)
    return {'files':dir_list}
      

@app.route('/Uploader',methods=['POST'])
def uploader():
    #上传
    import os

    from werkzeug.utils import secure_filename
    try:
      source_url = flask.request.referrer
      source_path = url_solution.split_query(source_url)
      # print(source_url,'url',source_path,'path')
      file = flask.request.files['file']
      # print(request.files)
      file.save(os.path.join(app.config['UPLOAD_FOLDER'], secure_filename(file.filename)))
      print(app.config['UPLOAD_FOLDER']+file.filename,'上传文件')
      #返回原页面
      # return "{}文件上传成功\n<a href='/{}?test=test'>跳转到原页面</a>".format(file.filename,source_url.split('/')[-1])
      return flask.redirect(source_path +'?tip=文件上传成功' )
    except:
      # return "{}文件上传出错\n<a href='/{}'>跳转到原页面</a>".format(file.filename,source_url.split('/')[-1])
      return flask.redirect(source_path +'?tip=文件上传出错' )

@app.route('/Del_file',methods=["POST"])
def delete_file():
  #删除文件
  import os
  source_url = flask.request.referrer
  source_path = url_solution.split_query(source_url)
  file_dict = flask.request.form.to_dict()
  file_list = list(file_dict.keys())
  error_tip = ''
  for file in file_list:
    if file in ['account','token','clientid']:
      continue
    if os.path.exists(app.config['UPLOAD_FOLDER']+file):  # 如果文件存在
      os.remove(app.config['UPLOAD_FOLDER']+file)
    else:
      error_tip += '{},'.format(file)
  if error_tip:
    #提示错误信息
    return flask.redirect(source_path+'?tip={}删除失败'.format(error_tip) )
  else:
    #回到原页面
    return flask.redirect(source_path+'?tip=文件删除成功')

@app.route('/initial', methods=["POST"])
def data_initialize():
    # print(flask.request.headers)
    # print(flask.request.json)
    # print(flask.request.data)
    # 获取数据并转化成字典
    initial_info = flask.request.form.to_dict()
    #获取源页面
    source_url = flask.request.referrer
    source_path = url_solution.split_query(source_url)
    import_type = initial_info.get("import_type")
    # print(source_url)
    #数据初始化
    if initial_info.get("mode") == 'file':#从文件获取 
      file_name = 'upload/' + initial_info.get("filename")
      data = dataSolution.loadInitialData('file',import_type,file_path='{}'.format(file_name))
      if type(data) == str:
        print(data)
        return flask.redirect(source_path + '?res=文件数据读取失败')
    elif initial_info.get("mode") == 'sql':#从数据库获取
      source_db = initial_info.get("database")
      source_tab = initial_info.get("table")
      data = dataSolution.loadInitialData('sql',import_type,source_db,source_tab)
    try:
      res = data.copy()
      if import_type == 'categroy':
        dataSolution.rebuild_tree(1,1,res)
      target_table = {'categroy':EnvSetting.categroy_tab,'page':EnvSetting.page_tab}[import_type]
      print(res,"res")
      sqlLink.dataToSql(res,EnvSetting.database,target_table,'append')
    except:
      #反馈错误
      return flask.redirect(source_path + '?res=入库失败')
    #按导入的数据提示
    type_tip = ('页面信息','分类数据')[import_type == 'categroy']
    #重定向到源页面
    return flask.redirect(source_path + '?res={}初始化成功'.format(type_tip))

@app.route('/dataAll')
def requestAll():
  #供应数据
  data = sqlLink.sqlGetData(EnvSetting.database,f'SELECT * FROM {EnvSetting.categroy_tab} ORDER BY Lft;')
  data = pd.DataFrame(data,columns=['node_id','name','lft','rgt','parent'])
  stack = []
  res = []
  for i in data.index:
    # check_data(i)
    if len(stack) > 0:
      while stack[-1] < data.loc[i]['rgt']:
        stack.pop()
        #重定向游标
        cur = res[0]
        for level in range(len(stack)-1):
          cur = cur['children'][-1]
        # print('RES',res)
        # print('当前cur',cur)
    if len(res) == 0:
      # dict = {'node_id':data.loc[i]['node_id'],'name':data.loc[i]['name'],'lft':data.loc[i]['lft'],'rgt':data.loc[i]['rgt'],'parent':data.loc[i]['parent'],'children':[]}
      dict = {'node_id':int(data.loc[i]['node_id']),'name':str(data.loc[i]['name']),'parent':int(data.loc[i]['parent']),'children':[]}
      res.append(dict)
      #让游标定为字典元素
      cur = res[0]
    else:
      dict = {'node_id':int(data.loc[i]['node_id']),'name':str(data.loc[i]['name']),'parent':int(data.loc[i]['parent']),'children':[]}
      #字典元素的子集列表添加一个字典
      cur['children'].append(dict)
      cur = cur['children'][-1]
    stack.append(data.loc[i]['rgt'])
  data = sqlLink.sqlGetData(EnvSetting.database,f'SELECT * FROM {EnvSetting.page_tab};')
  data = pd.DataFrame(data,columns=['page_id','page_name','page_link','categroy_id'])
  # 获取节点列表
  # categroy_list = data['categroy_id'].unique().tolist()
  page_dict = {}
  # 建立类型索引分组页面表
  for cate in data.groupby('categroy_id'):
    pages = []
    for i in range(cate[1].shape[0]) :
      # print(i,type(cate[1].iloc[i]['page_id']),type(cate[1].iloc[i]['page_name']))
      # page_list = [int(cate[1].iloc[i]['page_id']),str(page['page_name']),page['page_link'], page['categroy_id']]
      page_list = [ int(cate[1].iloc[i]['page_id']),
      cate[1].iloc[i]['page_name'],
      cate[1].iloc[i]['page_link'], 
      int(cate[1].iloc[i]['categroy_id']) ]
      pages.append(page_list)
    page_dict[cate[0]] = pages
  response_data = {'categroy':res,'pages':page_dict}
  return  jsonify(response_data)

@app.route('/checkPic', methods=["POST"])
def pic_spawn():
  # 验证码生成
  user_submit = flask.request.data
  # print(user_submit)
  str_info = user_submit.decode('utf-8')
  user_info = eval(str_info)
  client_id = user_info['clientid']
  # timestamp = user_info['timestamp']
  # print(timestamp)
  check_info = userSign.checkPicSpawn(client_id)
  pic_base64 = check_info[0]
  rand_code = check_info[1].lower()
  # 查询用户是否在库里
  search_id_sql = "select `id`,`client_id` FROM `{}`.`{}` WHERE client_id='{}';".format(EnvSetting.user_db,EnvSetting.client_tab,client_id)
  result = sqlLink.sqlGetData(EnvSetting.user_db,search_id_sql)
  if type(result) == str:
    return result
  # 用户验证码信息入库
  if result == ():
    # 添加用户
    import time
    localtime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    sql = "INSERT INTO `{}`.`{}`(`client_id`,`code`,`insert_time`) VALUES ('{}', '{}','{}')".format(EnvSetting.user_db,EnvSetting.client_tab,client_id,rand_code,localtime)
    res = sqlLink.InsertData(EnvSetting.user_db,sql)
  else:
    # 更新验证码
    sql = f"UPDATE `{EnvSetting.user_db}`.`{EnvSetting.client_tab}` SET `code` = '{rand_code}' WHERE client_id='{client_id}';"
    res = sqlLink.UpdateData(EnvSetting.user_db,sql)
  if res == 'insert error' or res == 'update error':
    return 'resetID'
  else:
    return pic_base64

@app.route('/register', methods=["POST"])
def user_sign_up():
  # 用户注册
  user_submit = flask.request.data
  print(user_submit,'用户提交')
  # 字符串信息转换成python字典
  user_info = eval(user_submit.decode('utf-8'))
  account = user_info['account']
  sql = "select `user_account` from `{}`.`{}` WHERE `user_account`='{}'".format(EnvSetting.user_db,EnvSetting.userInfo_tab,account)
  res = sqlLink.sqlGetData(EnvSetting.user_db,sql)
  if type(res) == str:
    return res
  elif res:
    return '用户已存在'
  pwd = userSign.idVerification(user_info)
  if type(pwd) == str:
    return pwd
  # 口令加盐入库
  salt = userSign.spawnSalt()
  hash_str = userSign.hs256Encode(salt,pwd)
  sql_info = "insert into `{}`.`{}`(`user_account`,`pwd`) VALUES ('{}','{}');".format(EnvSetting.user_db,EnvSetting.userInfo_tab,account,hash_str)
  sql_salt = "insert into `{}`.`{}`(`user_account`,`salt`) VALUES ('{}','{}');".format(EnvSetting.pass_db,EnvSetting.salt_tab,account,salt)
  result = sqlLink.InsertMultiData(EnvSetting.user_db,[sql_info,sql_salt])
  if result == 'insert error':
    return '注册失败'
  return '注册成功'

@app.route('/login', methods=["POST"])
def user_login():
  # 用户登录
  user_submit = flask.request.data
  print(user_submit,'用户提交')
  user_info = eval(user_submit.decode('utf-8'))
  account = user_info['account']
  # 身份验证
  submit_pwd = userSign.idVerification(user_info)
  if type(submit_pwd) == str:
    return submit_pwd
  # sql_info = "SELECT a.user_account,a.pwd,b.salt from {}.{} as A,{}.{} as B WHERE A.user_account=B.user_account and a.user_account='{}';".format(EnvSetting.user_db,EnvSetting.userInfo_tab,EnvSetting.pass_db,EnvSetting.salt_tab,account)
  sql_info = "SELECT a.user_account,a.pwd,a.grade,b.salt from {}.{} as A,{}.{} as B WHERE A.user_account=B.user_account and a.user_account='{}';".format(EnvSetting.user_db,EnvSetting.userInfo_tab,EnvSetting.pass_db,EnvSetting.salt_tab,account)
  res = sqlLink.sqlGetData(EnvSetting.user_db,sql_info)
  if type(res) == str:
    return res
  elif len(res)==0:
    return '用户不存在'
  # 提取用户口令密文
  user_pwd,user_grade,salt = res[0][1],res[0][2],res[0][3]
  # 加密提交的口令
  hash_str = userSign.hs256Encode(salt,submit_pwd)
  print(hash_str,'哈希')
  if user_pwd != hash_str:
    return '密码错误'
  # 生成用户令牌
  user_token = userSign.userTokenSave(account,user_info['clientid'])
  print(user_token,'用户令牌')
  if type(user_token) == str:
    return user_token
  # 提取用户邮箱信息
  sql = f"SELECT user_account,email,mail_show FROM `{EnvSetting.user_db}`.`{EnvSetting.userEmail_tab}` where user_account='{account}'"
  mail_res = sqlLink.sqlGetData(EnvSetting.user_db,sql)
  if type(mail_res) == str:
    return mail_res
  try:
    mail = mail_res[0][1]
    mail_show = mail_res[0][2]
  except:
    mail,mail_show=None,None
  if user_info['autosign'] == True:
    # 生成登录令牌
    login_token = userSign.loginTokenSave(account,user_info['clientid'])
    return {'login':login_token,'userToken':user_token[0],'grade':user_grade,'mail':mail,'mailShow':mail_show} if type(login_token) != str else login_token
  else:
    return {'login':'','userToken':user_token[0],'grade':user_grade,'mail':mail,'mailShow':mail_show}

@app.route('/tokenSign', methods=["POST"])
def token_release():
  # 用户自动登录验证
  user_submit = flask.request.data
  print(user_submit)
  user_info = eval(user_submit.decode('utf-8'))
  account = user_info['account']
  sql = f"select account,token,cast(starttime as char),cast(endtime as char),clientid from `{EnvSetting.user_db}`.`{EnvSetting.loginToken_tab}` where account='{account}';"
  res = sqlLink.sqlGetData(EnvSetting.user_db,sql)
  if type(res) == str or len(res)==0:
    return '自动登录失败'
  if res[0][1] == user_info['token'] and res[0][3] == user_info['endtime'] and res[0][4] == user_info['clientid'] and userSign.endTimeCheck(start_time=res[0][2],end_time=res[0][3]):
    # 身份验证通过
    sql = f"SELECT user_account,grade from {EnvSetting.user_db}.{EnvSetting.userInfo_tab} where `user_account`='{account}';"
    res = sqlLink.sqlGetData(EnvSetting.user_db,sql)
    if type(res) == str or len(res)==0:
      return '用户信息错误'
    user_grade = res[0][1]
    user_token = userSign.userTokenSave(account,user_info['clientid'])
    if type(user_token) == str:
      return user_token
    # 提取用户邮箱信息
    sql = f"SELECT user_account,email,mail_show FROM `{EnvSetting.user_db}`.`{EnvSetting.userEmail_tab}` where user_account='{account}'"
    mail_res = sqlLink.sqlGetData(EnvSetting.user_db,sql)
    if type(mail_res) == str:
      return mail_res
    mail = mail_res[0][1]
    mail_show = mail_res[0][2]
    return {'user':user_token[0],'grade':user_grade,'mail':mail,'mailShow':mail_show}
  else:
    print(res)
    return '自动登录已过期'

@app.route('/pwdChange', methods=["POST"])
def password_change():
  user_submit = flask.request.data
  print(user_submit)
  user_info = eval(user_submit.decode('utf-8'))
  account = user_info['account']
  pwd_list = userSign.idVerifyForChangePwd(user_info)
  if type(pwd_list) == str:
    return pwd_list
  last_word = pwd_list[0]
  new_word = pwd_list[1]
  # 匹配上次密码
  # get_pwd = f"select user_account,pwd FROM `{EnvSetting.user_db}`.`{EnvSetting.userInfo_tab}` where user_account='{account}';"
  sql_info = "SELECT a.user_account,a.pwd,a.grade,b.salt from {}.{} as A,{}.{} as B WHERE A.user_account=B.user_account and a.user_account='{}';".format(EnvSetting.user_db,EnvSetting.userInfo_tab,EnvSetting.pass_db,EnvSetting.salt_tab,account)
  res = sqlLink.sqlGetData(EnvSetting.user_db,sql_info)
  if type(res) == str:
    return res
  elif len(res)==0:
    return '用户不存在'
  # 提取用户口令密文
  user_pwd,salt = res[0][1],res[0][3]
  # 加密提交的旧口令
  hash_str = userSign.hs256Encode(salt,last_word)
  if hash_str == user_pwd:
    salt = userSign.spawnSalt()
    hash_str = userSign.hs256Encode(salt,new_word)
    update_pwd = f"update `{EnvSetting.user_db}`.`{EnvSetting.userInfo_tab}` set pwd='{hash_str}' where user_account='{account}';"
    update_salt = f"update `{EnvSetting.pass_db}`.`{EnvSetting.salt_tab}` set salt='{salt}' where user_account='{account}';"
    result = sqlLink.InsertMultiData(EnvSetting.user_db,[update_pwd,update_salt])
    if result == 'insert error':
      return '修改失败'
    return '密码修改成功'
  else:
    return '旧密码不匹配'

@app.route('/textChange', methods=["POST"])
def textChange():
  user_submit = flask.request.data
  print(user_submit)
  user_info = eval(user_submit.decode('utf-8'))
  sql = f"UPDATE `{EnvSetting.database}`.`{EnvSetting.content_tab}` SET `intro`='{user_info['content']}' WHERE `page_name`='{user_info['word']}';"
  res = sqlLink.OperateDataOnce(EnvSetting.database,sql)
  if res == 'operate OK':
    return 'Send Ok'
  else:
    return '词条更改失败'

@app.route('/userCheckMail', methods=["POST"])
def user_email_send():
  import model.timeSolute,model.mailCheck
  user_submit = flask.request.data
  print(user_submit)
  user_info = eval(user_submit.decode('utf-8'))
  # 验证用户token
  token_valid = userSign.userTokenCheck(user_info['account'],user_info['token'])
  if type(token_valid) == str:
    return token_valid
  # 检索用户提交记录
  sql = f"SELECT * FROM `{EnvSetting.user_db}`.`{EnvSetting.emailCheck_tab}` where `account`='{user_info['account']}'"
  res = sqlLink.sqlGetData(EnvSetting.user_db,sql)
  if type(res) == str:
    return res
  elif len(res)==0: # 首次提交
    endTime = model.timeSolute.deadlineComputer(EnvSetting.valid_mailCheckTime,EnvSetting.valid_mailCheckClass)['endTime']
    code = userSign.randCodeSpawn(4)
    sql = f"INSERT INTO `{EnvSetting.user_db}`.`{EnvSetting.emailCheck_tab}` (`account`, `code`, `email`, `endTime`) VALUES ('{user_info['account']}', '{code}', '{user_info['mail']}', '{endTime}')"
    res = sqlLink.OperateDataOnce(EnvSetting.user_db,sql)
    if res == 'operate OK':
      try:
        model.mailCheck.mailSend(user_info['mail'],code)
        return 'Send Ok'
      except:
        return '邮件发送失败'
    else:
      return '输入值错误'
  elif len(res)==1: # 更新验证码
    endTime = model.timeSolute.deadlineComputer(EnvSetting.valid_mailCheckTime,EnvSetting.valid_mailCheckClass)['endTime']
    code = userSign.randCodeSpawn(4)
    sql = f"UPDATE `{EnvSetting.user_db}`.`{EnvSetting.emailCheck_tab}` SET `code`='{code}',`email`='{user_info['mail']}',`endTime`='{endTime}' WHERE `account`='{user_info['account']}';"
    res = sqlLink.OperateDataOnce(EnvSetting.user_db,sql)
    if res == 'operate OK':
      try:
        model.mailCheck.mailSend(user_info['mail'],code)
        return 'Send Ok'
      except:
        return '邮件发送失败'
    else:
      return '输入值错误'
  else:
    return '输入值不规范'

@app.route('/userSetMail', methods=["POST"])
def user_email_save():
  # mail_info = flask.request.form.to_dict()
  user_submit = flask.request.data
  print(user_submit)
  mail_info = eval(user_submit.decode('utf-8'))
  # source_url = flask.request.referrer
  # source_path = url_solution.split_query(source_url)
  submit_user = mail_info['account']
  submit_mail = mail_info['mail']
  submit_captcha = mail_info['captcha']
  sql = f"SELECT code,email,startTime,endTime FROM `{EnvSetting.user_db}`.`{EnvSetting.emailCheck_tab}` where `account`='{submit_user}';"
  res = sqlLink.sqlGetData(EnvSetting.user_db,sql)
  code = res[0][0]
  email = res[0][1]
  start_time = str(res[0][2])
  end_time = str(res[0][3])
  if submit_mail != email or submit_captcha != code:
    print(submit_mail,email,submit_captcha,code)
    return '验证码错误'
  if not userSign.endTimeCheck(start_time,end_time):
    return '验证码失效'
  sql = f"SELECT user_account,email FROM `{EnvSetting.user_db}`.`{EnvSetting.userEmail_tab}` where `user_account`='{submit_user}'"
  res = sqlLink.sqlGetData(EnvSetting.user_db,sql)
  if type(res) == str:
    return res
  elif len(res)==0:
    sql = f"INSERT INTO `{EnvSetting.user_db}`.`{EnvSetting.userEmail_tab}` (`user_account`,`email`) VALUES ('{submit_user}', '{submit_mail}')"
    res = sqlLink.OperateDataOnce(EnvSetting.user_db,sql)
    if res == 'operate OK':
      return [email]
    else:
      return '邮箱绑定失败'
  elif len(res)==1:
    sql = f"UPDATE `{EnvSetting.user_db}`.`{EnvSetting.userEmail_tab}` SET `email`='{submit_mail}' WHERE `user_account`='{submit_user}';"
    res = sqlLink.OperateDataOnce(EnvSetting.user_db,sql)
    if res == 'operate OK':
      return [email]
    else:
      return '邮箱换绑失败'
  else:
    return '输入值不规范'



if __name__ == '__main__':
  app.run()
  # flask run --debugger --port 5001 --host 10.11.26.47  --cert=ca/server.crt --key=ca/server.key
  # --cert=ca/cert.pem --key=ca/key.pem